package com.zero.db;

import com.zero.xmodel.JdbcConfigPO;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * db执行器
 *
 * @author wuxi
 * @date 2020-07-28
 */
public class MysqlJdbc {

    private Connection conn;

    public MysqlJdbc(JdbcConfigPO po) {
        conn(po);
    }


    /**
     * Statement 和 PreparedStatement之间的关系和区别.
     * 关系：PreparedStatement继承自Statement,都是接口
     * 区别：PreparedStatement可以使用占位符，是预编译的，批处理比Statement效率高
     */
    private void conn(JdbcConfigPO po) {
        String url = String.format("jdbc:mysql://%s/%s?characterEncoding=utf-8", po.getHost(), po.getDbName());
        // 1.加载驱动程序
        try {
            Class.forName(po.getDriverName());
            // 2.获得数据库链接
            this.conn = DriverManager.getConnection(url, po.getUserName(), po.getPassword());
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 定制化只返回List<String> 类型的且无参数
     *
     * @param sql sql
     * @return List<String>
     */
    public List<String> executeToList(String sql) {
        List<String> result = new ArrayList<>();
        try (PreparedStatement statement = this.conn.prepareStatement(sql)) {
            if (Objects.isNull(this.conn)) {
                throw new RuntimeException("jdbc Connection 链接异常");
            }
            ResultSet rs = statement.executeQuery();
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                String value = rs.getString(1);
                result.add(value);
            }
            rs.close();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public <T> List<T> executeToList(String sql,
                                     List<String> params,
                                     Class<? extends T> aclass) {
        List<T> result = new ArrayList<>();
        if (Objects.isNull(this.conn)) {
            throw new RuntimeException("jdbc Connection 链接异常");
        }
        try (PreparedStatement statement = this.conn.prepareStatement(sql)) {
            if (null != params && params.size() > 0) {
                for (int i = 0; i < params.size(); i++) {
                    statement.setString(i, params.get(i));
                }
            }
            ResultSet rs = statement.executeQuery();
            // 4.处理数据库的返回结果(使用ResultSet类)
            //获取键名
            ResultSetMetaData md = rs.getMetaData();
            //反射
            Map<String, Field> objectInfo = getObjectInfo(aclass);
            //获取行的数量
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                T object = aclass.getDeclaredConstructor().newInstance();
                for (int i = 1; i < columnCount + 1; i++) {
                    String columnName = md.getColumnName(i).toLowerCase();
                    String value = rs.getString(i);
                    if (objectInfo.containsKey(columnName)) {
                        Field field = objectInfo.get(columnName);
                        field.setAccessible(true);
                        field.set(object, value);
                    }
                }
                result.add(object);
            }
            rs.close();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public void close() {
        try {
            // 关闭资源
            this.conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private <T> Map<String, Field> getObjectInfo(Class<? extends T> aclass) {
        Map<String, Field> fieldMap = new HashMap<>();
        Field[] fieldList = aclass.getDeclaredFields();
        for (Field field : fieldList) {
            fieldMap.put(field.getName().toLowerCase(), field);
        }
        return fieldMap;
    }
}